package org.example.mapper;

import org.apache.ibatis.annotations.*;
import org.example.pojo.*;

import java.util.List;

@Mapper
public interface VulnMapper {
    @Select("SELECT \n" +
            "    DATE(publish_time) AS date, \n" +
            "    COUNT(*) AS count \n" +
            "FROM \n" +
            "    vulnerabilities \n" +
            "WHERE \n" +
            "    publish_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)\n" +
            "GROUP BY \n" +
            "    DATE(publish_time)\n" +
            "ORDER BY \n" +
            "    date ASC;")
    List<VulnerabilityCount> countVulnerabilitiesByDate();
    @Select("SELECT \n" +
            "\tcount(*) as totalCount,\n" +
            "    SUM(CASE WHEN vuln_type like concat('%','系统','%') THEN 1 ELSE 0 END) AS sysCount,\n" +
            "    SUM(CASE WHEN vuln_type like concat('%','数据库','%') THEN 1 ELSE 0 END) AS dataCount,\n" +
            "    SUM(CASE WHEN vuln_type like concat('%','应用','%') THEN 1 ELSE 0 END) AS appCount,\n" +
            "    SUM(CASE WHEN severity like concat('%','INFO','%') THEN 1 ELSE 0 END) AS infoCount,\n" +
            "    SUM(CASE WHEN severity like concat('%','LOW','%') THEN 1 ELSE 0 END) AS lowCount,\n" +
            "    SUM(CASE WHEN severity like concat('%','MEDIUM','%') THEN 1 ELSE 0 END) AS mediumCount,\n" +
            "    SUM(CASE WHEN severity like concat('%','HIGH','%') THEN 1 ELSE 0 END) AS highCount,\n" +
            "    SUM(CASE WHEN severity like concat('%','SERIOUS','%') THEN 1 ELSE 0 END) AS serCount,\n" +
            "    SUM(CASE WHEN DATE(publish_time) = CURDATE() THEN 1 ELSE 0 END) AS todayCount,\n" +
            "    SUM(CASE WHEN `status` = '已修复' THEN 1 ELSE 0 END) AS fixCount\n" +
            "FROM \n" +
            "    vulnerabilities")
    VulnTypeCount countVulnByMonitor();
    @Insert("insert into vulnerabilities(temp_id, scan_history_id, cve_id, vuln_name, description, affected_product, vuln_type, weakness, severity, base_score, reference,suggestion, status,origin, publish_time) values(#{tempId}, #{scanHistoryId}, #{cveId},#{vulnName},#{description},#{affectedProduct},#{vulnType}, #{weakness}, #{severity}, #{baseScore}, #{reference},#{suggestion},#{status},#{origin}, #{publishTime})")
    void insert(Vulnerabilities vulnerabilities);
    void deleteVuln(List<Integer> ids);
    @Select("select * from vulnerabilities where id=#{id}")
    Vulnerabilities getVulnById(Integer id);
    @Insert("insert into vulnscantask(task_name, description, time_range, priority, excute_way, excute_time, day_of_week) values(#{taskName},#{description},#{timeRange},#{priority},#{excuteWay},#{excuteTime},#{dayOfWeek})")
    void addScanTask(VulnScanTask vulnScanTask);
    void updateScanTask(VulnScanTask vulnScanTask);
    void updatevulnscanhistory(ScanHistoryVO scanHistoryVO);
    List<Vulnerabilities> vulnList(Integer tempId,  Integer scanId, String cveId, String vulnName, String vulnType, String severity, String status, String start, String end, String keywords);
    List<VulnScanTask> scanList(String taskName, String start, String end, String status);
    @Insert("insert into vulnscanhistory(task_id,task_name, excute_way, excute_time, day_of_week) values(#{taskId},#{taskName},#{excuteWay},#{excuteTime},#{dayOfWeek})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void startScan(ScanHistoryVO task);
    @Select("select * from vulnscantask where id = #{taskId}")
    VulnScanTask getScanTaskById(Integer taskId);
    @Select("select * from vulnerabilities where scan_history_id = #{id}")
    List<Vulnerabilities> getVulnsByScanHisId(Integer id);
    List<ScanHistoryVO> getScanHistoryList(String taskName, String start, String end, String status);
    @Select("select count(*) from vulnerabilities where scan_history_id = #{id}")
    Integer getVulnNumByScanId(Integer id);
    @Select("select * from vulnscanhistory where id=#{id}")
    ScanHistoryVO getScanHistoryById(Integer id);
    void delScanTasks(List<Integer> ids);
    void delScanHistory(List<Integer> ids);
    @Insert("insert into vuln_template(name, description, vuln_num) values(#{name}, #{description}, #{vulnNum})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void addVulnTemp(VulnTempVO vulnTempVO);
    @Delete("delete from vuln_template where id=#{id}")
    void delVulnTemp(Integer id);
    @Delete("delete from vulnerabilities where temp_id=#{id}")
    void delVulnByTempId(Integer id);
    @Select("select * from vuln_template")
    List<VulnTempVO> getVulnTempList();

    void setVulnStatus(List<Integer> ids, String status);
}
